/*
	exec sp_save_osep_cml_fg_wip_lots 'R','CY28547LFXCT','7081027', null, '1791','09/08/2007'
*/
ALTER PROC sp_save_osep_cml_fg_wip_lots 
(
  
	@assyLoc        VARCHAR(50), 
	@deviceName     VARCHAR(50), 
	@lotNo          VARCHAR(50), 
	@splitLotNo     VARCHAR(50), 
	@qtyPulled      VARCHAR(50), 
	@recvDate       VARCHAR(50) 
)
  
  	AS
  	BEGIN
		DECLARE @LOTNUMBER VARCHAR(50)
		DECLARE @DATE_IN   VARCHAR(50)
		DECLARE @PROCESS_STEP_ID INT
	  	DECLARE @LOCATION_ID INT
	  	DECLARE @LOT_ID INT
		DECLARE @QTY_BALANCE VARCHAR(50)
		
		/* Set lotnumber as lotnumber + '-' + splitLotNo*/
		IF @SPLITLOTNO IS NOT NULL
		BEGIN
			SET @LOTNUMBER = @LOTNO + '-' + @SPLITLOTNO
		END
		ELSE		
		BEGIN
			SET @LOTNUMBER = @LOTNO
		END

 		IF @RECVDATE IS NULL
		BEGIN
			SET @RECVDATE = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
		END

		declare @count int

		IF @SPLITLOTNO IS NULL
 		BEGIN
			SELECT @count = COUNT(*) FROM  OSEP_CML_FG_WIP_HISTORY_TABLE WHERE DEVICE = @DEVICENAME AND LOTNO = @lotNo AND  QTY_PULLED = @QTYPULLED AND RECDATE = @RECVDATE AND SPLITLOTNO IS NULL
 		END
		ELSE
		BEGIN
 			SELECT @count = COUNT(*) FROM  OSEP_CML_FG_WIP_HISTORY_TABLE WHERE DEVICE = @DEVICENAME AND LOTNO = @lotNo AND SPLITLOTNO = @SPLITLOTNO AND QTY_PULLED = @QTYPULLED AND RECDATE = @RECVDATE
 		END
		
		IF ( @count = 0 )
  		BEGIN
			INSERT INTO OSEP_CML_FG_Wip_History_Table( AssyLoc,Device,LotNo,SplitLotNo,Qty_Pulled,RecDate )
  				VALUES( @assyLoc,@deviceName,@lotNo,@splitLotNo,@qtyPulled,@recvDate )

			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT, PROCESS_STEP WHERE PS_TYPE_NAME = 'FINISHED GOODS' AND PRODUCT.PART_NUMBER = @deviceName AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
		 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = 'OSEP'
		 	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
		  	BEGIN
		  		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @deviceName AND PS_TYPE_NAME = 'FINISHED GOODS' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
		 		IF @PROCESS_STEP_ID IS NULL
		  		BEGIN
		 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
		  			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		  			RETURN
		  		END
		 		ELSE
		 		BEGIN
		 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		 		END
		  	END
		 	ELSE
		 	BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		 	END 	
		  	
		  	SELECT @LOT_ID = LOT_ID , @DATE_IN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOTNUMBER
			
			IF @LOT_ID IS NOT NULL
			BEGIN
				IF CONVERT( DATETIME, @RECVDATE) > @DATE_IN
				BEGIN
					UPDATE LOT_TABLE SET DATE_IN = @RECVDATE WHERE LOT_ID = @LOT_ID
				END

				UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @qtyPulled, Lot_Current_Status = 'Running'  WHERE LOT_ID = @LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = IL_QTY_IN + @qtyPulled WHERE LOT_ID = @LOT_ID

				--if Null QTY_IN = QTY_BALANCE, when the Balance Qty IS zero OR some value then only updating 
				SELECT @QTY_BALANCE = IL_QTY_BALANCE FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
				IF @QTY_BALANCE IS NOT NULL
				BEGIN
					UPDATE INVENTORY_LOT SET IL_QTY_BALANCE = IL_QTY_BALANCE + @qtyPulled WHERE LOT_ID = @LOT_ID	
				END
				
				exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOTNUMBER, @LOTNUMBER, @qtyPulled, @recvDate
				
			END
			ELSE
			BEGIN
				/*When Lot Id is null, Creating a new Lot Id and sending alerts( Discrepancy, New Lot and No Assembly Location Vendor)
				 Note : If CML runs more than once a Day, Assembly Location vendor Notification 
				 should be added in below else condition*/
				EXEC SP_CREATE_UPDATE_OSEP_FG_LOT @DEVICENAME, @LOTNUMBER, @QTYPULLED, 'OSEP', 'Running', 'Finished Goods', @ASSYLOC, @RECVDATE
			END
		END
		ELSE
		BEGIN
			Update OSEP_CML_FG_Wip_History_Table set device = 'device' where device = 'device'			
			exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOTNUMBER, @LOTNUMBER, @qtyPulled, @recvDate
		END

		

END